package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.entity.RealTimeKanbanDO;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author sunyq
 * @date 2022/8/17 9:33
 */
@Slf4j
@Component(value = "realTimeKanbanDao")
public class RealTimeKanbanDao {
	@Resource(name = "dorisTemplate")
	private JdbcTemplate dorisTemplate;

	@Autowired
	private YunYingProperties yunYingProperties;

	public RealTimeKanbanDO summary(String format) {
		//todo
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT\n")
				.append(" ").append("IFNULL(a.deviceNums,0) as  deviceNums, -- 新增设备\n")
				.append(" ").append("IFNULL( a.accountNums,0) as accountNums , -- 新增账号\n")
//				.append(" ").append("b.newPayNums,-- 新账号充值人数\n")
//				.append(" ").append("b.newPayAmounts,-- 新账号充值金额\n")
				.append(" ").append("IFNULL( b.activeFeeAccounts,0) as activeFeeAccounts,-- 充值人数\n")
				.append(" ").append("IFNULL( b.activePayAmounts,0) as activePayAmounts,-- 充值总金额\n")
				.append(" ").append("IFNULL( b.activeAccounts,0) as activeAccounts, -- 活跃账号\n")
				.append(" ").append("round( IF ( b.activeAccounts > 0, divide ( b.activeFeeAccounts * 100.00, b.activeAccounts ), 0 ), 2 ) AS payFeeRate -- 付费率 充值人数/活跃账号\n")
				.append("FROM\n")
				.append(" ").append("(\n")
				.append(" ").append("  SELECT\n")
				.append(" ").append("    '").append(format).append("' AS DAY,\n")
				.append(" ").append("    a.deviceNums,\n")
				.append(" ").append("    b.accountNums \n")
				.append(" ").append("  FROM \n")
				.append(" ").append("    \t(\n")
				.append(" ").append("       SELECT\n")
				.append(" ").append("         COUNT( DISTINCT uuid ) AS deviceNums,\n")
				.append(" ").append("         a.reg_date AS `day` \n")
				.append(" ").append("       FROM\n")
				.append(" ").append("         ").append(yunYingProperties.getDailyDataDeviceRegTableData()).append(" a\n")
				.append(" ").append("        WHERE\n")
				.append(" ").append("          a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("          AND a.reg_date = '").append(format).append("'").append("\n")
				.append(" ").append("          GROUP BY\n")
				.append(" ").append("          	a.reg_date \n")
				.append(" ").append("        ) a\n")
				.append(" ").append("        FULL JOIN (\n")
				.append(" ").append("          SELECT\n")
				.append(" ").append("             COUNT( DISTINCT username ) AS accountNums,-- 新增账号\n")
				.append(" ").append("          	  reg_date AS `day` \n")
				.append(" ").append("          	FROM\n")
				.append(" ").append("          	  ").append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a\n")
				.append(" ").append("          	 WHERE\n")
				.append(" ").append("          	   a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("          	   AND a.reg_date = '").append(format).append("'").append("\n")
				.append(" ").append("          	  GROUP BY\n")
				.append(" ").append("               a.reg_date \n")
				.append(" ").append("            ) b USING ( `day` ) \n")
				.append(" ").append("       ) a\n")
				.append(" ").append("       FULL JOIN (\n")
				.append(" ").append("           SELECT\n")
				.append(" ").append("              `day`,\n")
//				.append(" ").append("              COUNT( DISTINCT newpaynum ) AS newPayNums,-- 新账号充值人数\n")
//				.append(" ").append("              sum( newpayamount ) AS newPayAmounts,-- 新账号充值金额\n")
				.append(" ").append("              COUNT( DISTINCT paynum ) AS activeFeeAccounts,-- 活跃付费总人数\n")
				.append(" ").append("              SUM( pay_amount ) AS activePayAmounts,-- 付费总金额\n")
				.append(" ").append("              COUNT( DISTINCT usename ) activeAccounts -- 活跃账号\n")
				.append(" ").append("            FROM\n")
				.append(" ").append("            	(\n")
				.append(" ").append("            	   SELECT\n")
				.append(" ").append("            	      a.active_date AS DAY,\n")
				.append(" ").append("            	      a.username usename,-- 活跃账号数,\n")
				.append(" ").append("            	      pay_amount,-- 付费金额\n")
				.append(" ").append("            	      IF( pay_amount > 0, a.username, NULL ) paynum -- 付费人数\n")
//				.append(" ").append("            	      IF( a.active_date = b.reg_date AND a.pay_amount > 0, a.username, NULL ) newpaynum,-- 新付费人\n")
//				.append(" ").append("            	      IF( a.active_date = b.reg_date AND a.pay_amount > 0, pay_amount, NULL ) newpayamount -- 新付费金额\n")
				.append(" ").append("            	    FROM\n")
				.append(" ").append("            	        (\n")
				.append(" ").append("            	          SELECT\n")
				.append(" ").append("            	            active_date,\n")
				.append(" ").append("            	            username,\n")
				.append(" ").append("            	            pay_amount,\n")
				.append(" ").append("            	            app_main,\n")
				.append(" ").append("            	            app_code \n")
				.append(" ").append("            	          FROM\n")
				.append(" ").append("            	             ").append(yunYingProperties.getDailyDataActiveTableData()).append(" a \n")
				.append(" ").append("                         WHERE\n")
				.append(" ").append("          	                a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append(" ").append("          	                AND a.active_date = '").append(format).append("'").append("\n")
				.append(" ").append("          	            ) a\n")
				.append(" ").append("          	     ) a\n")
				.append(" ").append("          	     GROUP BY  a.`day` \n")
				.append(" ").append("          ) b USING ( `day` )\n");

		log.info("实时看板汇总查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<RealTimeKanbanDO> realTimeKanbanDO = dorisTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(RealTimeKanbanDO.class));
		log.info("实时看板汇总查询耗时: {}ms", System.currentTimeMillis() - start);
		if (CollectionUtils.isNotEmpty(realTimeKanbanDO)){
			return realTimeKanbanDO.get(0);
		} else {
			return new RealTimeKanbanDO();
		}

	}

	public List<RealTimeKanbanDO> timeSharing(String format) {
		//
		//
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT\n")
				.append("  ").append("IFNULL(a.`hour`,b.`hour`) as datatime,\n")
				.append("  ").append("IFNULL(a.deviceNums,0) as  deviceNums, -- 新增设备\n")
				.append("  ").append("IFNULL( a.accountNums,0) as accountNums , -- 新增账号\n")
				.append("  ").append("IFNULL( b.activeFeeAccounts,0) as activeFeeAccounts,-- 充值人数\n")
				.append("  ").append("IFNULL( b.activePayAmounts,0) as activePayAmounts,-- 充值总金额\n")
				.append("  ").append("IFNULL( b.activeAccounts,0) as activeAccounts, -- 活跃账号\n")
				.append("  ").append("round( IF ( b.activeAccounts > 0, divide ( b.activeFeeAccounts * 100.00, b.activeAccounts ), 0 ), 2 ) AS payFeeRate -- 付费率 充值人数/活跃账号\n")
				.append("FROM\n")
				.append("  ").append("(\n")
				.append("  ").append("  SELECT\n")
				.append("  ").append("    '").append(format).append("' AS DAY,\n")
				.append("  ").append("    IFNULL(a.`hour`,b.`hour`) as `hour`,\n")
				.append("  ").append("    a.deviceNums,\n")
				.append("  ").append("    b.accountNums \n")
				.append("  ").append("  FROM \n")
				.append("  ").append("    \t(\n")
				.append("  ").append("       SELECT\n")
				.append("  ").append("         COUNT( DISTINCT uuid ) AS deviceNums,\n")
				.append("  ").append("         a.reg_date AS `day`, \n")
				.append("  ").append("         a.`hour` as `hour`\n")
				.append("  ").append("       FROM\n")
				.append("  ").append("         ").append(yunYingProperties.getDailyDataDeviceRegTableData()).append(" a\n")
				.append("  ").append("        WHERE\n")
				.append("  ").append("          a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append("  ").append("          AND a.reg_date = '").append(format).append("'").append("\n")
				.append("  ").append("          GROUP BY\n")
				.append("  ").append("          	a.reg_date ,  a.`hour` \n")
				.append("  ").append("        ) a\n")
				.append("  ").append("        FULL JOIN (\n")
				.append("  ").append("          SELECT\n")
				.append("  ").append("             COUNT( DISTINCT username ) AS accountNums,-- 新增账号\n")
				.append("  ").append("             a.reg_date AS `day` , \n")
				.append("  ").append("             a.`hour` as `hour` \n")
				.append("  ").append("          FROM\n")
				.append("  ").append("              ").append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a\n")
				.append("  ").append("          WHERE\n")
				.append("  ").append("             a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append("  ").append("             AND a.reg_date = '").append(format).append("'").append("\n")
				.append("  ").append("          GROUP BY\n")
				.append("  ").append("             a.reg_date , a.`hour`\n")
				.append("  ").append("         ) b USING ( `day`,`hour` ) \n")
				.append("  ").append("   ) a\n")
				.append("  ").append("   FULL JOIN (\n")
				.append("  ").append("       SELECT\n")
				.append("  ").append("          `day`,\n")
				.append("  ").append("          `hour`,\n")
				.append("  ").append("           COUNT( DISTINCT paynum ) AS activeFeeAccounts,-- 活跃付费总人数\n")
				.append("  ").append("           SUM( pay_amount ) AS activePayAmounts,-- 付费总金额\n")
				.append("  ").append("           COUNT( DISTINCT usename ) activeAccounts -- 活跃账号\n")
				.append("  ").append("       FROM\n")
				.append("  ").append("          (\n")
				.append("  ").append("            SELECT\n")
				.append("  ").append("               a.active_date AS DAY,\n")
				.append("  ").append("               a.`hour` as `hour`,\n")
				.append("  ").append("               a.username usename, -- 活跃账号数,\n")
				.append("  ").append("               a.pay_amount, -- 付费金额\n")
				.append("  ").append("               IF( a.pay_amount > 0, a.username, NULL ) paynum -- 付费人数\n")
				.append("  ").append("            FROM\n")
				.append("  ").append("            	 ").append(yunYingProperties.getDailyDataActiveTableData()).append(" a \n")
				.append("  ").append("            WHERE\n")
				.append("  ").append("          	  a.dx_app_code = ").append(yunYingProperties.getDxAPPCode()).append("\n")
				.append("  ").append("          	  AND a.active_date = '").append(format).append("'").append("\n")
				.append("  ").append("          ) a \n")
				.append("  ").append("        GROUP BY\n")
				.append("  ").append("          a.`day`,a.`hour` \n")
				.append("  ").append(") b USING ( `day`,`hour` ) \n");
		log.info("实时看板分时查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<RealTimeKanbanDO> realTimeKanbanDOList = dorisTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(RealTimeKanbanDO.class));
		log.info("实时看板分时查询耗时: {}ms", System.currentTimeMillis() - start);
		return realTimeKanbanDOList;
	}
}
